package cn.tataben.sharding.mappers.provider;

import java.lang.reflect.Field;
import java.util.Map;

import org.apache.ibatis.jdbc.SQL;
import org.springframework.boot.configurationprocessor.json.JSONArray;
import org.springframework.boot.configurationprocessor.json.JSONObject;

import cn.tataben.sharding.base.Generator;
import cn.tataben.sharding.domains.pos.UserPo;

public class UserMapperProvider {

	public String insert(UserPo user) {
		SQL sql = new SQL();
		sql.INSERT_INTO("`user`");

		if (user.getId() != null) {
			sql.VALUES("`id`", "#{id, jdbcType = INTEGER}");
		}

		if (user.getUsername() != null) {
			sql.VALUES("`username`", "#{username, jdbcType = VARCHAR}");
		}

		if (user.getPassword() != null) {
			sql.VALUES("`password`", "#{password, jdbcType = VARCHAR}");
		}

		return sql.toString();
	}

	public String update(UserPo user) {
		SQL sql = new SQL();
		sql.UPDATE("`user`");

		if (user.getUsername() != null) {
			sql.SET("`username` = #{username, jdbcType = VARCHAR}");
		}

		if (user.getPassword() != null) {
			sql.SET("`password` = #{password, jdbcType = VARCHAR}");
		}

		sql.WHERE("`id` = #{id, jdbcType = INTEGER}");

		return sql.toString();
	}

	public String selects(Map<String, Object> param) throws Exception {
		int page = Integer.parseInt(param.get("page").toString());
		int rows = Integer.parseInt(param.get("rows").toString());
		String order = param.get("order").toString();
		String query = param.get("query").toString();

		String columns = "`id`, `username`, `password` ";
		StringBuilder sb = new StringBuilder();
		sb.append("select ").append(columns);
		sb.append("from ").append("`user`");

		StringBuilder sbQuery = new StringBuilder();
		Field[] fields = UserPo.class.getDeclaredFields();
		if (query != null && query.length() > 0 && query.startsWith("{") && query.endsWith("}")) {
			JSONObject obj = new JSONObject(query);
			JSONArray keys = obj.names();
			boolean isString = false;
			JSONArray values = null;
			String value = "";
			String type = "";
			String key = "";
			if(null != keys){
				for (int k = 0; k < keys.length(); k++) {
					key = keys.getString(k);
					for (int i = 0; i < fields.length; i++) {
						if (fields[i].getName().equals(key)) {
							type = fields[i].getGenericType().getTypeName();
							if (type.equals("java.lang.String") || type.equals("java.util.Date")) {
								isString = true;
							}
							if (obj.get(key) instanceof String) {
								value = obj.getString(key);
								this.query(sbQuery, obj, isString, value, type, key);
							}
							if (obj.get(key) instanceof JSONArray ){
								values = obj.getJSONArray(key);
								for (int h = 0; h < values.length(); h++) {
									value = values.getString(h);
									this.query(sbQuery, obj, isString, value, type, key);
								}
							}
							break;
						}
					}
				}
			}
		}
		if(sbQuery.length() > 0){
			sb.append(" where ").append(sbQuery).append(" ");
		}

		StringBuilder sbOrder = new StringBuilder();
		if (order.length() > 0) {
			String sort = "";
			String column = "";
			String[] orders = order.split(",");
			for (int i = 0; i < orders.length; i++) {
				sort = orders[i].substring(0, 1);
				if (sort.equals("+") || sort.equals("-")) {
					column = Generator.camelToUnderline(orders[i].substring(1));
					if (columns.indexOf(column) > -1) {
						sbOrder.append("`").append(column).append("`");
						if (sort.equals("+")) {
							sbOrder.append(" ASC");
						} else {
							sbOrder.append(" DESC");
						}
						sbOrder.append(", ");
					}
				}
			}
			sbOrder.deleteCharAt(sbOrder.lastIndexOf(","));
			if (sbOrder.length() > 0) sb.append("order by ").append(sbOrder);
		}

		if (page > 0 && rows > 0) {
			int offset = (page - 1) * rows;
			sb.append("limit ").append(rows).append(" offset ").append(offset);
		}
		return sb.toString();
	}

	public String count(String query) throws Exception {
		StringBuilder sb = new StringBuilder();
		sb.append("select count(*) as num ");
		sb.append("from ").append("`user`");

		StringBuilder sbQuery = new StringBuilder();
		Field[] fields = UserPo.class.getDeclaredFields();
		if (query != null && query.length() > 0 && query.startsWith("{") && query.endsWith("}")) {
			JSONObject obj = new JSONObject(query);
			JSONArray keys = obj.names();
			boolean isString = false;
			JSONArray values = null;
			String value = "";
			String type = "";
			String key = "";
			if(null != keys){
				for (int k = 0; k < keys.length(); k++) {
					key = keys.getString(k);
					for (int i = 0; i < fields.length; i++) {
						if (fields[i].getName().equals(key)) {
							type = fields[i].getGenericType().getTypeName();
							if (type.equals("java.lang.String") || type.equals("java.util.Date")) {
								isString = true;
							}
							if (obj.get(key) instanceof String) {
								value = obj.getString(key);
								this.query(sbQuery, obj, isString, value, type, key);
							}
							if (obj.get(key) instanceof JSONArray ){
								values = obj.getJSONArray(key);
								for (int h = 0; h < values.length(); h++) {
									value = values.getString(h);
									this.query(sbQuery, obj, isString, value, type, key);
								}
							}
							break;
						}
					}
				}
			}
		}
		if(sbQuery.length() > 0){
			sb.append(" where ").append(sbQuery).append(" ");
		}

		return sb.toString();
	}

	private void query(StringBuilder sbQuery, JSONObject obj, boolean isString, String value, String type, String key) {
		String symbol = value.substring(1, 2);
		String ao = value.substring(0, 1);
		if("a".equals(ao) || "o".equals(ao)) {
			if ("<".equals(symbol) ||
				"=".equals(symbol) ||
				">".equals(symbol) ||
				"?".equals(symbol) ||
				"-".equals(symbol) ||
				"+".equals(symbol)) {
				value = value.substring(2);
				if (sbQuery.length() > 0) {
					if ("a".equals(ao)) {
						sbQuery.append(" and ");
					} else if ("o".equals(ao)) {
						sbQuery.append(" or ");
					}
				}

				sbQuery.append("`").append(Generator.camelToUnderline(key)).append("`");
				if (symbol.equals("?")) {
					sbQuery.append(" like '%").append(value).append("%'");
				} else if (symbol.equals("-")) {
					if (isString) {
						sbQuery.append(" >= '").append(value).append("'");
					} else {
						sbQuery.append(" >= ").append(value);
					}
				} else if (symbol.equals("+")) {
					if (isString) {
						sbQuery.append(" <= '").append(value).append("'");
					} else {
						sbQuery.append(" <= ").append(value);
					}
				} else {
					if (isString) {
						sbQuery.append(" ").append(symbol).append(" '").append(value).append("'");
					} else {
						sbQuery.append(" ").append(symbol).append(" ").append(value);
					}
				}
			}
		}
	}
}